package work;


//import java.awt.print.Book;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import java.sql.Statement;





	public class DBDao {
	    private DBConnection dbConnection = null;
	    private Connection conn = null;
	   /* private static String username;
	    private static String password;*/
	    static Scanner input = new Scanner(System.in);

	    public DBDao() {
	        this.dbConnection =new DBConnection();
	        this.conn = dbConnection.getConnection();
	    }

	    //管理员登陆
	    public boolean denglu(){
	        System.out.println("----管理员登录----");
	        System.out.println("输入用户名：");
	        String adminName=input.next();
	        System.out.println("输入密码：");
	        String adminPwd=input.next();
	        String sql="SELECT ADMIN_NAME,ADMIN_PWD FROM ADMIN WHERE ADMIN_NAME=? AND ADMIN_PWD=?";
	        PreparedStatement ptmt= null;
	        try {
	            ptmt = conn.prepareStatement(sql);
	            ptmt.setString(1, adminName);
	            ptmt.setString(2, adminPwd);
	            ResultSet rs=ptmt.executeQuery();
	            if (rs.next()) {
	                System.out.println("登录成功！");
	                return true;
	            } else {
	                System.out.println("账号或密码不正确，请重新登录！\n");
	                return false;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        return false;
	    }

	    //管理员注册
	    public boolean zhuce(){
	        System.out.println("----管理员注册----");
	        System.out.println("输入用户名：");
	        String adminName=input.next();
	        System.out.println("输入密码：");
	        String adminPwd1=input.next();
	        System.out.println("再次确认密码");
	        String adminPwd2=input.next();

	        if(adminPwd1.equals(adminPwd2)){
	            String password=adminPwd1;
	            String sql="INSERT INTO ADMIN(ADMIN_NAME,ADMIN_PWD) VALUES(?,?)";
	            PreparedStatement ptmt= null;
	            try {
	                ptmt = conn.prepareStatement(sql);
	                ptmt.setString(1, adminName);
	                ptmt.setString(2, password);
	                ptmt.execute();
	                System.out.println("注册成功！");
	                return true;
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }else{
	            System.out.println("你输入的两次密码不一致，请重新注册!");
	            return false;
	        }
	       return false;
	    }

	    //新图书入库
	    public boolean insertBook(Book book){
	        boolean flag = false;
	        PreparedStatement pstmt = null;
	        String sql = "insert into book(book_id,book_name,book_author,book_press) values(?,?,?,?)";
	        try {
	            pstmt = conn.prepareStatement(sql); //模糊查询
	            pstmt.setInt(1, book.getBook_id());
	            pstmt.setString(2,book.getBook_name());
	            pstmt.setString(3, book.getBook_author());
	            pstmt.setString(4,book.getBook_press());
	            if (pstmt.executeUpdate() > 0) {
	                flag = true;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return flag;
	    }


	    //查询所有图书信息
	    public boolean bookInof(){

	        Statement stmt =null;

	        String sql="select * from book";
	        try{
	            stmt=conn.createStatement();
	            //执行查询数据库的SQL语句   ，返回一个结果集（ResultSet）对象。
	            ResultSet Book = stmt.executeQuery(sql);
	            System.out.println("图书编号"+"   书名"+"                               " +
	                    "作者"+"                  出版社");
	            while(Book.next()){ //如果对象中有数据，就会循环打印出来

	                System.out.printf("%-10s",Book.getString("book_id"));
	                System.out.printf("%-30s",Book.getString("book_name"));
	                System.out.printf("%-20s",Book.getString("book_author"));
	                System.out.printf("%-20s\n",Book.getString("book_press"));
	            }
	        }catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                stmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return true;

	    }

	    //查询该图书编号图书信息
	    public  boolean searchBook(int id){
	        PreparedStatement pstmt=null;
	        String sql="SELECT book_id,book_name,book_author,book_press FROM book WHERE book_id=?";
	        try{
	            pstmt=conn.prepareStatement(sql);
	            pstmt.setInt(1, id);

	            ResultSet rs=pstmt.executeQuery();
	            while(rs.next()){
	                System.out.println("图书编号："+rs.getInt(1)+"\t"+"书名："+rs.getString(2)+"\t"+"图书作者："+rs.getString(3)+"\t"+"出版社："+rs.getString(4));
	            }
	        }catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return true;

	    }

	    //用图书编号修改图书信息
	    public boolean bookUpdate(int id,Book book){
	        boolean flag=false;
	        PreparedStatement pstmt = null;
	        String sql="UPDATE book SET book_name=?,book_author=?,book_press=? WHERE book_id=?";
	        try{
	            pstmt=conn.prepareStatement(sql);
	            pstmt.setString(1, book.getBook_name());
	            pstmt.setString(2, book.getBook_author());
	            pstmt.setString(3, book.getBook_press());
	            pstmt.setInt(4, book.getBook_id());
	            if (pstmt.executeUpdate() > 0) {
	                flag = true;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return flag;

	    }


	    //按编号删除旧图书
	    public boolean delelteBook(int id){
	        boolean flag=false;
	        PreparedStatement pstmt=null;
	        String sql="DELETE FROM book WHERE book_id=?";

	        try{
	            pstmt=conn.prepareStatement(sql);
	            pstmt.setInt(1, id);     //接收id传入sql语句
	            if (pstmt.executeUpdate() > 0) {        // 至少已经更新了一行
	                flag = true;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return flag;

	    }


	    //办理借阅证
	    public boolean  loanCard(User user){
	        boolean flag = false;
	        PreparedStatement pstmt = null;
	        String sql = "insert into user(user_id,user_name,user_age,user_sex,user_phone) values(?,?,?,?,?)";
	        try {
	            pstmt = conn.prepareStatement(sql); //模糊查询
	            pstmt.setInt(1, user.getUser_id());
	            pstmt.setString(2, user.getUser_name());
	            pstmt.setInt(3, user.getUser_age());
	            pstmt.setString(4, String.valueOf(user.getUser_sex()));
	            pstmt.setString(5, user.getUser_phone());

	            if (pstmt.executeUpdate() > 0) {
	                flag = true;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return flag;
	    }

	    //用借书证搜索读者

	    public boolean searchUser(int id){
	        PreparedStatement pstmt=null;
	        String sql="SELECT user_id,user_name,user_age,user_sex,user_phone FROM user WHERE user_id=?";
	        try{
	            pstmt=conn.prepareStatement(sql);
	            pstmt.setInt(1, id);

	            ResultSet rs=pstmt.executeQuery();
	            while(rs.next()){
	                System.out.println("读者借书证号："+rs.getInt(1)+"\t"+"读者姓名："+rs.getString(2)+"\t"+"读者年龄："+rs.getString(3)+"\t"+"读者性别"+rs.getString(4)+"\t"+"读者联系方式"+rs.getString(5));
	            }
	        }catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return true;
	    }

	    //搜索所有拥有借书证读者
	    public boolean userInof(){
	        Statement stmt =null;

	        String sql="select * from user";
	        try{
	            stmt=conn.createStatement();
	            //执行查询数据库的SQL语句   ，返回一个结果集（ResultSet）对象。
	            ResultSet Book = stmt.executeQuery(sql);
	            System.out.println("借书证号"+"   读者姓名"+"                 " +
	                    "读者年龄"+"  读者性别"+"      联系方式");
	            while(Book.next()){ //如果对象中有数据，就会循环打印出来

	                System.out.printf("%-10s",Book.getString("user_id"));
	                System.out.printf("%20s",Book.getString("user_name"));
	                System.out.printf("%20s",Book.getString("user_age"));
	                System.out.printf("%20s",Book.getString("user_sex"));
	                System.out.printf("%20s\n",Book.getString("user_phone"));
	            }
	        }catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                stmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return true;

	    }

	    //借书
	    public boolean borrow(Borrow borrow){
	        if(serachBorrow(borrow.getBook_id())){
	            return false;
	        }
	        boolean flag = false;
	        PreparedStatement pstmt = null;
	        String sql = "insert into borrow(user_id,book_id) values(?,?)";
	        try {
	            pstmt = conn.prepareStatement(sql); //模糊查询
	            pstmt.setInt(1, borrow.getUser_id());
	            pstmt.setInt(2,borrow.getBook_id());

	            if (pstmt.executeUpdate() > 0) {
	                flag = true;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return flag;
	    }

	    //还书
	    public boolean return_book(Borrow borrow){
	        boolean flag=false;
	        PreparedStatement pstmt=null;
	        String sql="DELETE FROM borrow WHERE user_id=? and book_id=?";

	        try{
	            pstmt=conn.prepareStatement(sql);
	            pstmt.setInt(1, borrow.getUser_id());
	            pstmt.setInt(2, borrow.getBook_id());
	            if (pstmt.executeUpdate() > 0) {
	                flag = true;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                pstmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return flag;


	    }

	        //在借阅表中搜索该图书
	    public boolean serachBorrow(int book_id){
	        String sql="SELECT book_id FROM borrow WHERE book_id=?";
	        PreparedStatement ptmt= null;
	        try {
	            ptmt = conn.prepareStatement(sql);
	            ptmt.setInt(1, book_id);

	            ResultSet rs=ptmt.executeQuery();
	            if (rs.next()) {
	                System.out.println("图书已被借走！\n");
	                return true;
	            } else {
	                return false;
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        return false;
	    }
	}